package com.xcyy.zjdxmjk.zjdxmjk.repository;

import com.xcyy.zjdxmjk.zjdxmjk.entity.House;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

public interface HouseRepository  extends JpaRepository<House ,String> {
    House save(House house);
    List<House> findByTownCode(String townCode);

    House findByDateAndStageAndTypeAndTownCode(Date date, Integer stage, Integer type,String code);

    @Query(value = " SELECT h.type,h.stage,sum(h.count) from house h GROUP BY h.type,h.stage",nativeQuery = true)
    List<Object> countHouse();

    @Query(value = " select  h.stage,h.type,sum(h.count)  from house h where   h.town_code like ?1% GROUP BY h.stage,h.type",nativeQuery = true)
    List<Object> countQjdcAndBdccl(String code);

    @Query(value = " SELECT h.stage,sum(h.count) from house h where h.town_code in(?1) GROUP BY h.stage",nativeQuery = true)
    List<Object> findByDept(List<String> codes );

    @Query(value = " SELECT h.town_code,h.stage,sum(h.count) from house h where h.town_code =?1 GROUP BY h.town_code, h.stage",nativeQuery = true)
    List<Object> findByDeptAndStage(String code );

    @Query(value = " SELECT h.stage,h.type,sum(h.count) from house h where h.date between ?1 and ?2 GROUP BY h.stage,h.type",nativeQuery = true)
    List<Object> findByDate(Date startDate, Date endDate);

    //@Query(value = " select DATE_FORMAT(h.date,'%Y-%m-%d'),sum(h.count) from house h WHERE DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= DATE(h.date) and h.town_code like ?1% and h.stage =?2 and h.type=?3 GROUP BY DATE_FORMAT(h.date,'%Y-%m-%d') ORDER BY DATE_FORMAT(h.date,'%Y-%m-%d') ASC",nativeQuery = true)
    @Query(value = " SELECT date,count from( select DATE_FORMAT(h.date,'%Y-%m-%d') date,sum(h.count) count from house h WHERE h.town_code like ?1% and h.stage =?2 and h.type=?3 GROUP BY DATE_FORMAT(h.date,'%Y-%m-%d') ORDER BY DATE_FORMAT(h.date,'%Y-%m-%d') DESC LIMIT 7) t ORDER BY date",nativeQuery = true)
    List<Object> findByCodeAndStageAndType(String code, String stage,String type);
}
